import json
from io import BytesIO

import pandas as pd
from datetime import datetime, timedelta
import match
import os

import logging
import pathlib

from utils.verifyParameters import verify

# 配置日志
logging.basicConfig(level=logging.INFO)


def get_closest_sheet(file_path, station_config, recon_end_time):
    def html_to_excel_preserve_names(input_file):
        """将 HTML 文件转换为 Excel 对象，尝试保持原始表名"""
        try:
            # 读取 HTML 表格，保留表名信息
            tables = pd.read_html(input_file)

            # 尝试提取表名（如果HTML中有caption或title）
            table_names = []
            # 这里可以添加提取表名的逻辑，或者使用默认名称

            excel_buffer = BytesIO()
            sheet_names = []

            with pd.ExcelWriter(excel_buffer, engine='openpyxl') as writer:
                for i, df in enumerate(tables):
                    # 尝试使用有意义的表名，回退到默认名称
                    if i < len(table_names) and table_names[i]:
                        sheet_name = str(table_names[i])[:31]
                    else:
                        sheet_name = f'Table_{i + 1}'[:31]

                    df.to_excel(writer, sheet_name=sheet_name, index=False)
                    sheet_names.append(sheet_name)

            excel_buffer.seek(0)
            print(f"成功转换: 共转换了 {len(tables)} 个表格")
            return excel_buffer, sheet_names

        except Exception as e:
            print(f"转换失败: {e}")
            return None, None

    def safe_read_excel(file_path_or_buffer, sheet_name=0, **kwargs):
        """安全地读取Excel文件或BytesIO对象"""
        try:
            # 先尝试默认引擎
            return pd.read_excel(file_path_or_buffer, sheet_name=sheet_name, **kwargs)
        except Exception:
            try:
                # 失败后尝试 openpyxl
                return pd.read_excel(file_path_or_buffer, sheet_name=sheet_name, engine="openpyxl", **kwargs)
            except Exception as e:
                print(f"读取Excel失败: {e}")
                return None

    sheet_names = None
    excel_buffer = None
    try:
        # 尝试直接读取 Excel 文件
        excel_buffer = pd.ExcelFile(file_path)
        sheet_names = excel_buffer.sheet_names
        print(f"直接读取 Excel 成功，sheet_names: {sheet_names}")

    except Exception as e:
        print(f"直接读取 Excel 失败，尝试 HTML 转 Excel: {e}")

        # 使用 html_to_excel 转换 HTML
        excel_buffer, sheet_names = html_to_excel_preserve_names(file_path)
        if excel_buffer is None:
            raise RuntimeError("文件既不是标准 Excel，也无法从 HTML 转换")
        print(f"HTML 转 Excel 成功，sheet_names: {sheet_names}")

    print(f"工作表名称: {sheet_names}")

    closest_sheet = None
    min_diff = None
    closest_sheet_name = None
    for sheet in sheet_names:
        header_row = None
        df_tmp = safe_read_excel(excel_buffer, sheet_name=sheet)
        if all(item in df_tmp.columns.tolist() for item in list(station_config["columns"].values())):
            header_row = -1
        else:
            for row in df_tmp.head(3).itertuples():
                if all(item in list(row[1:]) for item in list(station_config["columns"].values())):
                    header_row = row.Index
                    break
        if header_row is not None:
            df = safe_read_excel(excel_buffer, sheet_name=sheet, skiprows=header_row + 1)
            try:
                df[station_config["columns"]["create_time"]] = pd.to_datetime(
                    df[station_config["columns"]["create_time"]], errors="coerce"
                )
                df = df.dropna(subset=[station_config["columns"]["create_time"]])
                df = df.dropna(subset=[station_config["columns"]["gas_num"]])
                latest_time = df[station_config["columns"]["create_time"]].max()
                if pd.notnull(latest_time):
                    time_diff = abs(
                        (latest_time.date() - datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S").date()).days
                    )
                    if min_diff is None or time_diff < min_diff:
                        min_diff = time_diff
                        closest_sheet = df
                        closest_sheet_name = sheet
            except Exception as e:
                logging.error(f"处理{sheet}时出错: {e}")
    return closest_sheet, closest_sheet_name


def process_baotoushixin_reconciliation(recon_start_time, recon_end_time, file_path, fault_tolerant, station_id, ignore_time):
    """
        处理包头石兴对账单的特殊逻辑

        Args:
            station_id: 站点ID
            fault_tolerant: 差异公斤数
            recon_start_time (str): 对账开始时间，格式为 "YYYY-MM-DD HH:MM:SS"
            recon_end_time (str): 对账结束时间，格式为 "YYYY-MM-DD HH:MM:SS"
            file_path (str): 上传的Excel文件路径

        Returns:
            dict: 处理结果的JSON数据
        """
    try:
        # region 配置
        station_config = {
            "name": "包头",
            "ids": [230],
            "main_body_gas_station": 132,
            "file_keyword": "(.*)包头(.*)",
            "columns": {
                "create_time": "时间",
                # "car_number": "车牌号",
                "gas_num": "气量",
                "money": "金额",
            },
            "diff_num": 1,  # 差异在x公斤以内的设置为疑似匹配
        }

        verify(fault_tolerant, station_id, ignore_time, station_config)

        # 处理时间范围
        start_time = datetime.strptime(recon_start_time, "%Y-%m-%d %H:%M:%S")
        start_time = (start_time - timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
        end_time = datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S")
        end_time = (end_time + timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")

        closest_sheet, closest_sheet_name = get_closest_sheet(file_path, station_config, recon_end_time)
        logging.info(f"{len(closest_sheet)} {closest_sheet_name}")

        # 打印完整的处理后数据结构信息
        logging.info(f"处理后的数据信息:")
        logging.info(f"数据形状: {closest_sheet.shape}")
        logging.info(f"列名: {list(closest_sheet.columns)}")

        # 创建用于显示的数据副本并确保日期正确格式化
        # 显示所有数据
        display_data = closest_sheet[list(station_config['columns'].values())].copy()
        display_data[station_config["columns"]["create_time"]] = display_data[
            station_config["columns"]["create_time"]].dt.strftime('%Y-%m-%d %H:%M:%S')
        logging.info(f"数据预览:\n{display_data.to_json(orient='records', force_ascii=False, default_handler=str)}")

        try:
            station_data = closest_sheet[list(station_config["columns"].values())]
            station_data = match.set_station_id_column(station_data, station_config)
            # 特殊处理 单价字段不正确 进行金额除以气量 设置单价列
            station_data = match.set_price_column(station_data, station_config)
        except Exception as e:
            logging.error('配置文件与excel不一致')
            raise RuntimeError('配置文件与excel不一致') from e
        logging.info(f"{len(station_data)} {closest_sheet_name}")
        station_dfs_dict = {}

        # step 2 处理Excel 如果是多个气站并且Excel中的字段配置包含station_name，就需要分多个气站进行对比。
        if "station_name" in station_config["columns"] and len(station_config["ids"]) > 1 and "nms" in station_config:
            station_names = station_config["nms"]
            station_name_col = station_config["columns"]["station_name"]
            for idx, pattern in enumerate(station_names):
                matched_df = station_data[station_data[station_name_col].astype(str).str.match(pattern, na=False)]
                # 如果有详细时间，需要对excel数据按照详细时间进行排序
                non_midnight_count = (
                        matched_df[station_config["columns"]["create_time"]].dt.strftime("%H:%M:%S") != "00:00:00"
                ).sum()
                if len(matched_df) > 0 and (non_midnight_count / len(matched_df)) > 0.5:
                    # 超过50%即为"绝大多数"
                    matched_df = matched_df.sort_values(by=station_config["columns"]["create_time"])
                station_dfs_dict[station_config["ids"][idx]] = matched_df

        match_result = []

        # step 3 如果有station_dfs_dict就循环这个，然后每个站点单独获取数据并对比，否则就获取配置中的所有站点数据并进行对比
        if len(station_dfs_dict) > 0:
            for i in station_dfs_dict:
                online_data = match.get_online_data([i], start_time, end_time)
                # 开始对比
                match_result.append(match.match_data_v1(station_dfs_dict[i], online_data, station_config))
        else:
            # excel获取时间区间内的数据
            if station_config.get("excel_get_time_range", False):
                station_data = station_data[
                    station_data[station_config["columns"]["create_time"]].between(start_time, end_time)]
            online_data = match.get_online_data(station_config['ids'], start_time, end_time)
            # 开始对比
            match_result.append(match.match_data_v1(station_data, online_data, station_config))

        # 生成JSON结果
        json_result = match.create_json_result_v2(match_result, station_config, start_time, end_time)
        return json_result

    except Exception as e:
        raise Exception(f"处理包头石兴对账单时出错: {str(e)}")